/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.core.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.core.dao.ClinicDAO;
import com.core.database.Manager;
import com.core.entities.Clinic;
import com.core.exception.DAOException;

/**
 * 
 * @author himanasser
 */
public class ClinicDAPImpl implements ClinicDAO {
		Clinic clinic = new Clinic();

	ArrayList<Clinic> clinicList = new ArrayList<Clinic>();

	Connection connection = null;
	private static ClinicDAPImpl instance=null;
	public static  ClinicDAPImpl getInstance(){
		if(instance==null){
			instance=new ClinicDAPImpl();
		}
		return instance;
	}

	@Override
	public Clinic findByName(String name) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM clinic WHERE name = '" + name + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					clinic.setId(rs.getInt(1));
					clinic.setName(rs.getString(2));
					clinic.setMinistry_license_number(rs.getString(3));
					clinic.setManager_id(rs.getInt(4));
					clinic.setDepartment_id(rs.getInt(5));
					clinic.setTime_frame_id(rs.getInt(6));
					clinic.setDay_off_id(rs.getInt(7));
					clinic.setEmployee_id(rs.getInt(8));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return clinic;
	}

	@Override
	public Clinic findByMinistry_license_number(String ministry_license_number)
			throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM clinic WHERE ministry_license_number = '"
					+ ministry_license_number + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					clinic.setId(rs.getInt(1));
					clinic.setName(rs.getString(2));
					clinic.setMinistry_license_number(rs.getString(3));
					clinic.setManager_id(rs.getInt(4));
					clinic.setDepartment_id(rs.getInt(5));
					clinic.setTime_frame_id(rs.getInt(6));
					clinic.setDay_off_id(rs.getInt(7));
					clinic.setEmployee_id(rs.getInt(8));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return clinic;
	}

	@Override
	public List<Clinic> findByManager_id(int manager_id) throws DAOException {
		
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM clinic WHERE manager_id = '"
					+ manager_id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					clinic.setId(rs.getInt(1));
					clinic.setName(rs.getString(2));
					clinic.setMinistry_license_number(rs.getString(3));
					clinic.setManager_id(rs.getInt(4));
					clinic.setDepartment_id(rs.getInt(5));
					clinic.setTime_frame_id(rs.getInt(6));
					clinic.setDay_off_id(rs.getInt(7));
					clinic.setEmployee_id(rs.getInt(8));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return clinicList;
	}

	@Override
	public List<Clinic> findByDepartment_id(int department_id) throws DAOException {		
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM clinic WHERE department_id = '"
					+ department_id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					clinic.setId(rs.getInt(1));
					clinic.setName(rs.getString(2));
					clinic.setMinistry_license_number(rs.getString(3));
					clinic.setManager_id(rs.getInt(4));
					clinic.setDepartment_id(rs.getInt(5));
					clinic.setTime_frame_id(rs.getInt(6));
					clinic.setDay_off_id(rs.getInt(7));
					clinic.setEmployee_id(rs.getInt(8));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return clinicList;
	}

	@Override
	public List<Clinic> findByTime_frame_id(int time_frame_id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM clinic WHERE time_frame_id = '"
					+ time_frame_id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					clinic.setId(rs.getInt(1));
					clinic.setName(rs.getString(2));
					clinic.setMinistry_license_number(rs.getString(3));
					clinic.setManager_id(rs.getInt(4));
					clinic.setDepartment_id(rs.getInt(5));
					clinic.setTime_frame_id(rs.getInt(6));
					clinic.setDay_off_id(rs.getInt(7));
					clinic.setEmployee_id(rs.getInt(8));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return clinicList;
	}

	@Override
	public List<Clinic> findByDay_off_id(int day_off_id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM clinic WHERE day_off_id = '"
					+ day_off_id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					clinic.setId(rs.getInt(1));
					clinic.setName(rs.getString(2));
					clinic.setMinistry_license_number(rs.getString(3));
					clinic.setManager_id(rs.getInt(4));
					clinic.setDepartment_id(rs.getInt(5));
					clinic.setTime_frame_id(rs.getInt(6));
					clinic.setDay_off_id(rs.getInt(7));
					clinic.setEmployee_id(rs.getInt(8));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return clinicList;
	}

	@Override
	public List<Clinic> findByEmployee_id(int employee_id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT * FROM clinic WHERE employee_id = '"
					+ employee_id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					clinic.setId(rs.getInt(1));
					clinic.setName(rs.getString(2));
					clinic.setMinistry_license_number(rs.getString(3));
					clinic.setManager_id(rs.getInt(4));
					clinic.setDepartment_id(rs.getInt(5));
					clinic.setTime_frame_id(rs.getInt(6));
					clinic.setDay_off_id(rs.getInt(7));
					clinic.setEmployee_id(rs.getInt(8));

				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return clinicList;
	}

	@Override
	public void insert(Clinic o) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = " INSERT INTO clinic (name, ministry_license_number, manager_id, department_id, "
					+ "time_frame_id, day_off_id, employee_id) VALUE (?, ?, ?, ?, ?, ?, ?)";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, o.getName());
			ps.setString(2, o.getMinistry_license_number());
			ps.setInt(3, o.getManager_id());
			ps.setInt(4, o.getDepartment_id());
			ps.setInt(5, o.getTime_frame_id());
			ps.setInt(6, o.getDay_off_id());
			ps.setInt(7, o.getEmployee_id());
			int result = ps.executeUpdate();
			if (result != 1) {
				throw new SQLException("no row inserted!");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			throw new RuntimeException("Cannot add new Clinic", ex);
		} finally {
			Manager.closeConnection(connection);
		}

	}

	@Override
	public Clinic findById(int id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = "SELECT *FROM clinic WHERE id = '" + id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while (rs.next()) {
				count++;
				if (count == 1) {
					clinic.setId(rs.getInt(1));
					clinic.setName(rs.getString(2));
					clinic.setMinistry_license_number(rs.getString(3));
					clinic.setManager_id(rs.getInt(4));
					clinic.setDepartment_id(rs.getInt(5));
					clinic.setTime_frame_id(rs.getInt(6));
					clinic.setDay_off_id(rs.getInt(7));
					clinic.setEmployee_id(rs.getInt(8));

					System.out.println(clinic);
				}
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
		return clinic;
	}

	@Override
	public List<Clinic> findAll() throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = " SELECT * FROM clinic ";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				clinic.setId(rs.getInt(1));
				clinic.setName(rs.getString(2));
				clinic.setMinistry_license_number(rs.getString(3));
				clinic.setManager_id(rs.getInt(4));
				clinic.setDepartment_id(rs.getInt(5));
				clinic.setTime_frame_id(rs.getInt(6));
				clinic.setDay_off_id(rs.getInt(7));
				clinic.setEmployee_id(rs.getInt(8));

				clinicList.add(clinic);

				System.out.println(clinic);
			}
		} catch (SQLException s) {
			s.printStackTrace();
		}
		return clinicList;
	}

	@Override
	public void delete(int id) throws DAOException {
		try {
			connection = Manager.getConnection();
			String sql = " DELETE FROM clinic WHERE id = '" + id + "'";
			PreparedStatement ps = connection.prepareStatement(sql);
			int result = ps.executeUpdate();
			if (result != 1) {
				ps.setString(1, clinic.getName());
				ps.setString(2, clinic.getMinistry_license_number());
				ps.setInt(3, clinic.getManager_id());
				ps.setInt(4, clinic.getDepartment_id());
				ps.setInt(5, clinic.getTime_frame_id());
				ps.setInt(6, clinic.getDay_off_id());
				ps.setInt(7, clinic.getEmployee_id());
			}

		} catch (SQLException s) {
			s.printStackTrace();

		}
	}

	@Override
	public void update(Clinic o) throws DAOException {

		try {
			connection = Manager.getConnection();
			String sql = "UPDATE city SET name = '" + o.getName()
					+ "', ministry_license_number = '"
					+ o.getMinistry_license_number() + "', manager_id = '"
					+ o.getManager_id() + "',department_id = '"
					+ o.getDepartment_id() + "', time_frame_id = '"
					+ o.getTime_frame_id() + "', day_off_id = '"
					+ o.getDay_off_id() + "', employee_id = '"
					+ o.getEmployee_id() + "' WHERE id = " + o.getId();
			PreparedStatement ps = connection.prepareStatement(sql);
			int result = ps.executeUpdate(sql);

			if (result != 1) {
				ps.setString(1, o.getName());
				ps.setString(2, o.getMinistry_license_number());
				ps.setInt(3, o.getManager_id());
				ps.setInt(4, o.getDepartment_id());
				ps.setInt(5, o.getTime_frame_id());
				ps.setInt(6, o.getDay_off_id());
				ps.setInt(7, o.getEmployee_id());
			}

		} catch (SQLException s) {
			s.printStackTrace();
		}
	}
}
